package wtc.stu.dao;

import wtc.stu.entity.StuEntity;
import wtc.stu.util.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StuDaoIntegratedQuery {
    private DBConnection dbc;
    private Connection conn = null;
    private ResultSet rs = null;
    private PreparedStatement pre = null;
    public StuDaoIntegratedQuery()
    {
        dbc = new DBConnection();

    }
    // 综合查询学生信息
    public List<StuEntity> getAll()
    {
        List<StuEntity> ar = new ArrayList<StuEntity>();
        try
        {
            conn = dbc.getConnection();
            String sql = "select * from IntegratedQuery";
            pre = conn.prepareStatement(sql);
            rs = pre.executeQuery();
            while (rs.next())
            {
                StuEntity IntegratedQuery = new StuEntity();
                IntegratedQuery.setSid(rs.getInt("sid"));
                IntegratedQuery.setSname(rs.getString("sname"));
                IntegratedQuery.setAge(rs.getInt("age"));
                IntegratedQuery.setCname(rs.getString("cname"));
                IntegratedQuery.setGname(rs.getString("gname"));
                IntegratedQuery.setXname(rs.getString("xname"));
                IntegratedQuery.setZname(rs.getString("zname"));
                IntegratedQuery.setTname(rs.getString("tname"));
                IntegratedQuery.setSremark(rs.getString("sremark"));
                ar.add(IntegratedQuery);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            dbc.closeAll(pre, conn, rs);
        }
        return ar;
    }
    // 综合查询成绩信息
    public List<StuEntity> getAllScore()
    {
        List<StuEntity> ar = new ArrayList<StuEntity>();
        try
        {
            conn = dbc.getConnection();
            String sql = "select * from IntegratedQueryScore";
            pre = conn.prepareStatement(sql);
            rs = pre.executeQuery();
            while (rs.next())
            {
                StuEntity IntegratedQueryScore = new StuEntity();
                IntegratedQueryScore.setSid(rs.getInt("sid"));
                IntegratedQueryScore.setSname(rs.getString("sname"));
                IntegratedQueryScore.setCname(rs.getString("cname"));
                IntegratedQueryScore.setGname(rs.getString("gname"));
                IntegratedQueryScore.setXname(rs.getString("xname"));
                IntegratedQueryScore.setZname(rs.getString("zname"));
                IntegratedQueryScore.setScore(rs.getInt("score"));
                ar.add(IntegratedQueryScore);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            dbc.closeAll(pre, conn, rs);
        }
        return ar;
    }
}
